package com.zr.gdms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.zr.gdms.dao.ScoreDao;

import com.zr.gdms.util.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**
 * 成绩逻辑实现层
 * @author 李培宁
 *
 */
public class ScoreDaoImpl implements ScoreDao{

	@Override
	public int getScoreCount() {
		int sccount = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(score.scid) AS sccount");
		sql.append(" FROM score");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				sccount = rs.getInt("sccount");
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sccount;
	}

	public JSONArray getScores(int page, int rows) {
		JSONArray ja = new JSONArray();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT score.scid,score.sid,student.sname,score.cid,course.cname,score.score");
		sql.append(" FROM score");
		sql.append(" INNER JOIN student");
		sql.append(" ON score.sid = student.sid");
		sql.append(" INNER JOIN  course");
		sql.append(" ON score.cid = course.cid");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				JSONObject j = new JSONObject();
				j.put("scid", rs.getInt("scid"));
				j.put("sid", rs.getInt("sid"));
				j.put("sname", rs.getString("sname"));
				j.put("cid", rs.getInt("cid"));
				j.put("cname", rs.getString("cname"));
				j.put("score", rs.getInt("score"));
				ja.add(j);
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return ja;
	}

	@Override
	public JSONArray searchScore(int page, int rows, int score1, int score2) {
		JSONArray ja = new JSONArray();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT score.scid,score.sid,student.sname,score.cid,course.cname,score.score");
		sql.append(" FROM score");
		sql.append(" INNER JOIN student");
		sql.append(" ON score.sid = student.sid");
		sql.append(" INNER JOIN  course");
		sql.append(" ON score.cid = course.cid");
		sql.append(" WHERE score.score BETWEEN ? AND ?");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, score1);
			pst.setInt(2, score2);
			pst.setInt(3, (page-1)*rows);
			pst.setInt(4, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				JSONObject j = new JSONObject();
				j.put("scid", rs.getInt("scid"));
				j.put("sid", rs.getInt("sid"));
				j.put("sname", rs.getString("sname"));
				j.put("cid", rs.getInt("cid"));
				j.put("cname", rs.getString("cname"));
				j.put("score", rs.getInt("score"));
				ja.add(j);
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return ja;
	}
}
